import lombok.Getter;
import lombok.Setter;

import java.sql.*;

import java.text.SimpleDateFormat;

import java.util.Collection;
import java.util.StringJoiner;

import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.BlockingQueue;
import java.util.concurrent.RecursiveAction;
import java.util.concurrent.TimeUnit;

public class DBConnection extends RecursiveAction implements AutoCloseable {
    public static final int MULTI_INSERT_LIMIT = 1024;
    public static final int DB_TIMEOUT = 30;    // sec

    private static String dbName = "learn";
    private static String dbUser = "user";
    private static String dbPass = "qazWSX123!@#";

    @Setter
    @Getter
    private static String engine = "MEMORY";

    @Setter
    private String sqlOnExit;
    private Connection connection;
    @Getter
    private BlockingQueue<String> sqlQueue;

    public DBConnection() throws SQLException {
        connection = getConnection();
        sqlQueue = new ArrayBlockingQueue<>(10);
    }

    @Override
    public void close() throws Exception {
        if (connection != null) {
            connection.close();
        }
    }

    @Override
    protected void compute() {
        try {
            String sql;

            while (true) {
                sql = sqlQueue.poll(DB_TIMEOUT, TimeUnit.SECONDS);

                if (sql == null) {
                    System.err.println("\nNo data timeout");
                    break;
                }

                if (sql.length() == 0) {
                    // End of data
                    break;
                }

                executeSQL(sql);
            }

            if (sql != null && sqlOnExit != null && !sqlOnExit.isBlank()) {
                executeSQL(sqlOnExit);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    protected void executeSQL(String sql) throws SQLException {
        executeSQL(connection, sql);
    }

    public static Connection getConnection() throws SQLException {
        Connection connection;

        try {
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/" + dbName, dbUser, dbPass
            );

            executeSQL(connection, "SET max_heap_table_size=8589934592;");
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }

        return connection;
    }

    public static void executeSQL(Connection connection, String sql) throws SQLException {
        try (
                Statement statement = connection.createStatement()
        ) {
            statement.execute(sql);
        } catch (SQLException e) {
            System.err.println(System.lineSeparator() + sql);
            throw e;
        }
    }

    public static String countVisitsSQL(Collection<Visit> visits) {
        SimpleDateFormat sqlDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sqlTimeFormat = new SimpleDateFormat("HH:mm:ss");

        StringJoiner joiner = new StringJoiner(", ");
        StringBuilder builder = new StringBuilder();

        for (Visit visit : visits) {
            builder.append("(")
                    .append(visit.getStation().toString())
                    .append(", '")
                    .append(sqlDateFormat.format(visit.getTime()))
                    .append("', '")
                    .append(sqlTimeFormat.format(visit.getTime()))
                    .append("', '")
                    .append(sqlTimeFormat.format(visit.getTime()))
                    .append("')");

            joiner.add(builder);
            builder.setLength(0);
        }

        return builder.append("INSERT INTO station_work_time(station, `date`, `from`, `to`) VALUES ")
                .append(joiner)
                .append(" AS new(s, d, f, t) ON DUPLICATE KEY UPDATE")
                .append(" `from` = IF(`from` > f, f, `from`),")
                .append(" `to` = IF(`to` < t, t, `to`)")
                .toString();
    }

    public static String countVotersSQL(Collection<Visit> voters) {
        SimpleDateFormat sqlDateFormat = new SimpleDateFormat("yyyy-MM-dd");

        StringJoiner joiner = new StringJoiner(", ");
        StringBuilder builder = new StringBuilder();

        for (Voter voter : voters) {
            builder.append("('")
                    .append(voter.getName())
                    .append("', '")
                    .append(sqlDateFormat.format(voter.getBirthDay()))
                    .append("', 1)");

            joiner.add(builder);
            builder.setLength(0);
        }

        return builder.append("INSERT INTO voter_count(name, birthDate, `count`) VALUES ")
                .append(joiner)
                .append(" ON DUPLICATE KEY UPDATE `count` = `count` + 1")
                .toString();
    }

    public static void countVoter(String name, String birthDay) throws SQLException {
        birthDay = birthDay.replace('.', '-');
        String sql =
            "SELECT id FROM voter_count WHERE birthDate='" + birthDay + "' AND name='" + name + "'";
        ResultSet rs = DBConnection.getConnection().createStatement().executeQuery(sql);
        if (!rs.next()) {
            DBConnection.getConnection().createStatement()
                .execute("INSERT INTO voter_count(name, birthDate, `count`) VALUES('" +
                    name + "', '" + birthDay + "', 1)");
        } else {
            Integer id = rs.getInt("id");
            DBConnection.getConnection().createStatement()
                .execute("UPDATE voter_count SET `count`=`count`+1 WHERE id=" + id);
        }
        rs.close();
    }

    public static void printVoterCounts() throws SQLException {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy.MM.dd");

        StringBuilder builder = new StringBuilder();

        String sql = "SELECT name, birthDate, `count` FROM voter_count WHERE `count` > 1 ORDER BY name";
        try (
            Statement statement = getConnection().createStatement()
        ) {
            ResultSet rs = statement.executeQuery(sql);

            while (rs.next()) {
                builder.append("\t")
                        .append(rs.getString("name"))
                        .append(" (")
                        .append(dateFormat.format(rs.getDate("birthDate")))
                        .append(") - ")
                        .append(rs.getInt("count"))
                        .append(System.lineSeparator());
            }

            System.out.print(builder);
        }
    }

    public static void printStationWorkTimes() throws SQLException {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy.MM.dd");
        SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm");

        StringBuilder builder = new StringBuilder();

        String sql = "SELECT station, `date`, `from`, `to` FROM station_work_time ORDER BY station, `date`";
        try (
                Statement statement = getConnection().createStatement()
        ) {
            ResultSet rs = statement.executeQuery(sql);
            int station = 0;
            StringJoiner joiner = null;

            while (rs.next()) {
                if (rs.getInt("station") != station) {
                    if (station > 0) {
                        builder.append("\t")
                                .append(station)
                                .append(" - ")
                                .append(joiner)
                                .append(System.lineSeparator());
                    }
                    joiner = new StringJoiner(", ");
                    station = rs.getInt("station");
                }

                joiner.add(
                        new StringBuilder()
                                .append(dateFormat.format(rs.getDate("date")))
                                .append(" ")
                                .append(timeFormat.format(rs.getTime("from")))
                                .append("-")
                                .append(timeFormat.format(rs.getTime("to")))
                );
            }

            if (station > 0) {
                builder.append("\t")
                        .append(station)
                        .append(" - ")
                        .append(joiner)
                        .append(System.lineSeparator());
            }

            System.out.print(builder);
        }
    }
}
